# -*- coding: utf-8 -*-
import pymssql

# 数据库连接信息
DB_CONFIG = {
    'server': '(local)',
    'user': 'sa',
    'password': '123456789',
    'database': 'JCDGXT',
    'charset': 'utf8'
}

# 初始化数据库连接
def get_db_connection():
    return pymssql.connect(
        server=DB_CONFIG['server'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database'],
        charset=DB_CONFIG['charset']
    )

# 获取所有书商信息
def all():
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute('SELECT ISBN, jname, jauthor, price, stock FROM [jiaocai$]')
    result = cursor.fetchall()
    conn.close()
    # 手动进行编码转换
    for row in result:
        row['jname'] = row['jname'].encode('latin1').decode('gbk')
        row['jauthor'] = row['jauthor'].encode('latin1').decode('gbk')
    return result

# 插入书商信息
def insert_jiaocai(ISBN, jname, jauthor, price, stock):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('INSERT INTO [jiaocai$] (ISBN, jname, jauthor, price, stock) VALUES (%s, %s, %s, %s, %s)', (ISBN, jname, jauthor, price, stock))
    conn.commit()
    conn.close()

def jiaocai_exists(ISBN):
    jiaocai = all()
    return any(item['ISBN'] == ISBN for item in jiaocai)


# 根据书商号查询信息
def query_by_ISBN(ISBN):
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute('SELECT jname, jauthor, price, stock FROM [jiaocai$] WHERE ISBN = %s', (ISBN,))
    result = cursor.fetchone()
    conn.close()
    if result:
        result['jname'] = result['jname'].encode('latin1').decode('gbk')
    if result:
        result['jauthor'] = result['jauthor'].encode('latin1').decode('gbk')
    return result

# 根据书商号更新信息
def update_by_ISBN(ISBN, data):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('UPDATE [jiaocai$] SET jname=%s, jauthor=%s, price=%s, stock=%s WHERE ISBN=%s', (data['jname'], data['jauthor'], data['price'], data['stock'], ISBN))
    conn.commit()
    conn.close()